﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using DSPL.DataAccess;
using Microsoft.Practices.Unity;
using System.Diagnostics;
using System.Windows.Forms;
using DSPL.Contracts;
using DSPL.Contracts.Interfaces;
using DSPL.Services.Help;
using DSPL.Contracts;
using DSPL.Contracts.Common;

namespace DSPL.Services.Services
{
    public class TransactionReadService : ITransactionReadService
    {
        DatabaseAccess db;

        #region ITransactionReadService Members
                

        

        #endregion


        public DataTable GetPurchaseOrderTaxData(int primaryKey)
        {
            var readMasterData = DatabaseAccess.Instance;
            string sqlQuery = "Select * from fPoTaxDt where RefPk=@referenceNo";
            var paramList = new List<Parameter>
                                 {
                                     new Parameter{ParamName="@referenceNo",ParamValue=primaryKey},
                                 };


            var purchaseQuery = new Query { QueryString = sqlQuery, QType = QueryType.SELECTOTHER, ParameterList = paramList.ToArray() };

            var dtPurchaseDet = readMasterData.ExcecuteSelect(purchaseQuery, true);
            return dtPurchaseDet;

        }

        public DataTable GetGoodReceiveTaxData(int primaryKey)
        {
            var readMasterData = DatabaseAccess.Instance;
            string sqlQuery = "Select * from fGrnTaxDt where GrnPk=@referenceNo";
            var paramList = new List<Parameter>
                                 {
                                     new Parameter{ParamName="@referenceNo",ParamValue=primaryKey},
                                 };


            var grnTaxQuery = new Query { QueryString = sqlQuery, QType = QueryType.SELECTOTHER, ParameterList = paramList.ToArray() };

            var dtGrnTaxDet = readMasterData.ExcecuteSelect(grnTaxQuery, true);
            return dtGrnTaxDet;

        }

        public DataTable GetGoodReceiveImportExpense(int primaryKey)
        {
            var db = DatabaseAccess.Instance;
            string sqlQuery = "Select * from fGrnImExp where GrnPk=@referenceNo";
            var paramList = new List<Parameter>
                                 {
                                     new Parameter{ParamName="@referenceNo",ParamValue=primaryKey},
                                 };


            var grnImpExpQuery = new Query { QueryString = sqlQuery, QType = QueryType.SELECTOTHER, ParameterList = paramList.ToArray() };

            var dtGrnImpTaxDet = db.ExcecuteSelect(grnImpExpQuery, true);
            return dtGrnImpTaxDet;

        }

        public DataTable GetGrnCreditNotes(int primaryKey)
        {
            var db = DatabaseAccess.Instance;
            string sqlQuery = "Select * from fCCrNote where CcrPk=@referenceNo";
            var paramList = new List<Parameter>
                                 {
                                     new Parameter{ParamName="@referenceNo",ParamValue=primaryKey},
                                 };


            var grnCreditNotesQuery = new Query { QueryString = sqlQuery, QType = QueryType.SELECTOTHER, ParameterList = paramList.ToArray() };

            var dtCreditNotes = db.ExcecuteSelect(grnCreditNotesQuery, true);
            return dtCreditNotes;

        }


        public DataTable GetPurchaseOrderDetails(int primaryKey)
        {
            var db = DatabaseAccess.Instance;

            List<ColumnWithValue> columnList = new List<ColumnWithValue>();
            columnList.Add(new ColumnWithValue() { ColumnName = "PoPk" });
            columnList.Add(new ColumnWithValue() { ColumnName = "TxnDate" });
            columnList.Add(new ColumnWithValue() { ColumnName = "CurPk" });
            columnList.Add(new ColumnWithValue() { ColumnName = "LocationPk" });          

            List<ColumnWithValue> whereList = new List<ColumnWithValue>();
            whereList.Add(new ColumnWithValue() { ColumnName = "PoPk", Comparison = QComparison.EQUALS, ColumnValue = primaryKey });                     

            var purchaseOrderQuery = new Query { TableName="fPoHed", QType = QueryType.SELECT, ColValues=columnList.ToArray(), WhereClause=whereList.ToArray() };

            var dtPoHeader = db.ExcecuteSelect(purchaseOrderQuery);
            return dtPoHeader;

        }

        public bool IsGrnAlreadyApproved(string refNo)
        {
            var db = DatabaseAccess.Instance;

           List<ColumnWithValue> columnList  = new List<ColumnWithValue>();
           columnList.Add(new ColumnWithValue{ColumnName="RefNo2"});

           List<ColumnWithValue> whereList = new List<ColumnWithValue>();
           whereList.Add(new ColumnWithValue { ColumnName = "RefNo2", Comparison = QComparison.EQUALS, ColumnValue = refNo });

           var purchaseOrderQuery = new Query { TableName="fGrnhed", QType = QueryType.SELECT, ColValues=columnList.ToArray(), WhereClause=whereList.ToArray() };
           var dtPoHeader = db.ExcecuteSelect(purchaseOrderQuery);

          return (dtPoHeader.Rows.Count > 0)? true: false;
          
        }

        public DataTable GetQuotationTaxData(int quotationKey)
        {
            var readMasterData = DatabaseAccess.Instance;

            List<ColumnWithValue> whereList = new List<ColumnWithValue>();
            List<ColumnWithValue> columnList = new List<ColumnWithValue>();

            columnList.Add(new ColumnWithValue { ColumnName = "fTax.TaxCode" });
            columnList.Add(new ColumnWithValue { ColumnName = "fTax.TaxName" });
            columnList.Add(new ColumnWithValue { ColumnName = "fQuoTaxRg.TaxRegNo" });
            columnList.Add(new ColumnWithValue { ColumnName = "fQuoTaxRg.TaxRegNo As ChkTaxRegNo" });

            List<ColumnWithValue> joinList = new List<ColumnWithValue>();
            joinList.Add(new ColumnWithValue { FromTable = "fQuoTaxRg", ToTable = "fTax", FromColumn = "TaxPk", ToColumn = "TaxPk", JoinType = QJoinType.INNERJOIN, Comparison = QComparison.EQUALS });

            whereList.Add(new ColumnWithValue { ColumnName = "fQuoTaxRg.QuoPk", Comparison = QComparison.EQUALS, ColumnValue = quotationKey });
            var quotationTaxQuery = new Query { TableName = "fQuoTaxRg", QType = QueryType.SELECT, ColValues = columnList.ToArray(), WhereClause = whereList.ToArray(), JoinStructure = joinList.ToArray() };
            return readMasterData.ExcecuteSelect(quotationTaxQuery);
        }

        public DataTable GetSalesOrderTaxData(int orderKey)
        {
            var readMasterData = DatabaseAccess.Instance;

            List<ColumnWithValue> whereList = new List<ColumnWithValue>();
            List<ColumnWithValue> columnList = new List<ColumnWithValue>();

            columnList.Add(new ColumnWithValue { ColumnName = "fTax.TaxCode" });
            columnList.Add(new ColumnWithValue { ColumnName = "fTax.TaxName" });
            columnList.Add(new ColumnWithValue { ColumnName = "fOrdTaxRg.TaxRegNo" });
            columnList.Add(new ColumnWithValue { ColumnName = "fOrdTaxRg.TaxRegNo As ChkTaxRegNo" });

            List<ColumnWithValue> joinList = new List<ColumnWithValue>();
            joinList.Add(new ColumnWithValue { FromTable = "fOrdTaxRg", ToTable = "fTax", FromColumn = "TaxPk", ToColumn = "TaxPk", JoinType = QJoinType.INNERJOIN, Comparison = QComparison.EQUALS });

            whereList.Add(new ColumnWithValue { ColumnName = "fQuoTaxRg.QuoPk", Comparison = QComparison.EQUALS, ColumnValue = orderKey });
            var salesOrderTaxQuery = new Query { TableName = "fQuoTaxRg", QType = QueryType.SELECT, ColValues = columnList.ToArray(), WhereClause = whereList.ToArray(), JoinStructure = joinList.ToArray() };
            return readMasterData.ExcecuteSelect(salesOrderTaxQuery);
        }

        public DataTable GetInvoiceTaxData(int invoiceKey)
        {
            var readMasterData = DatabaseAccess.Instance;

            List<ColumnWithValue> whereList = new List<ColumnWithValue>();
            List<ColumnWithValue> columnList = new List<ColumnWithValue>();

            columnList.Add(new ColumnWithValue { ColumnName = "fTax.TaxCode" });
            columnList.Add(new ColumnWithValue { ColumnName = "fTax.TaxName" });
            columnList.Add(new ColumnWithValue { ColumnName = "fInvTaxRg.TaxRegNo" });
            columnList.Add(new ColumnWithValue { ColumnName = "fInvTaxRg.TaxRegNo As ChkTaxRegNo" });

            List<ColumnWithValue> joinList = new List<ColumnWithValue>();
            joinList.Add(new ColumnWithValue { FromTable = "fInvTaxRg", ToTable = "fTax", FromColumn = "TaxPk", ToColumn = "TaxPk", JoinType = QJoinType.INNERJOIN, Comparison = QComparison.EQUALS });

            whereList.Add(new ColumnWithValue { ColumnName = "fInvTaxRg.InvPk", Comparison = QComparison.EQUALS, ColumnValue = invoiceKey });
            var invoiceTaxQuery = new Query { TableName = "fInvTaxRg", QType = QueryType.SELECT, ColValues = columnList.ToArray(), WhereClause = whereList.ToArray(), JoinStructure = joinList.ToArray() };
            return readMasterData.ExcecuteSelect(invoiceTaxQuery);
        }

        public bool ValidatePurchaseOrder(Modes formMode,int primaryKey)
        {
            bool isSuccess = true;
            string keyCode = string.Empty;
            ReadService readService = new ReadService();
            var db = DatabaseAccess.Instance;

            if (formMode == Modes.Cancel || formMode == Modes.Edit || formMode == Modes.Approve)
            {           
                List<ColumnWithValue> whereCondition = new List<ColumnWithValue>();
                whereCondition.Add(new ColumnWithValue { ColumnName = "Balqty", Comparison = QComparison.EQUALS, ColumnValue = 0.00, ColumnConnector =QLogicOperator.AND });
                whereCondition.Add(new ColumnWithValue { ColumnName = "PoPk", Comparison = QComparison.EQUALS, ColumnValue = primaryKey, ColumnConnector = QLogicOperator.AND });

                List<ColumnWithValue> fields = new List<ColumnWithValue>();
                fields.Add(new ColumnWithValue { ColumnName = "PoPk" });
                fields.Add(new ColumnWithValue { ColumnName = "balQty" });

                var purchaseQuery = new Query { TableName = "fPoDet", QType = QueryType.SELECT, ColValues = fields.ToArray(), WhereClause = whereCondition.ToArray() };

                if (db.ExcecuteSelect(purchaseQuery).Rows.Count >0)
                {          
                    MessageBox.Show("PO has been already allocated to GRN.", "Purchase Order",MessageBoxButtons.OK,MessageBoxIcon.Information);
                    return false;
               }

                List<ColumnWithValue> whereConditionGrnHed = new List<ColumnWithValue>();
                whereConditionGrnHed.Add(new ColumnWithValue { ColumnName = "PoPk", Comparison = QComparison.EQUALS, ColumnValue = primaryKey });
                List<ColumnWithValue> displayFieldsGrnHed = new List<ColumnWithValue>();
                displayFieldsGrnHed.Add(new ColumnWithValue { ColumnName = "PoPk" });

                var grnQuery = new Query { TableName = "fGrnHed", QType = QueryType.SELECT, ColValues = displayFieldsGrnHed.ToArray(), WhereClause = whereConditionGrnHed.ToArray() };

                if (db.ExcecuteSelect(grnQuery).Rows.Count > 0)
                {
                    MessageBox.Show("PO has been already allocated to GRN.", "Purchase Order", MessageBoxButtons.OK, MessageBoxIcon.Information);
                   return false;
                  }
                else
                {
                    var grnTQuery = new Query { TableName = "fGrnTHed", QType = QueryType.SELECT, ColValues = displayFieldsGrnHed.ToArray(), WhereClause = whereConditionGrnHed.ToArray() };
                    if (db.ExcecuteSelect(grnTQuery).Rows.Count > 0)
                    {
                        MessageBox.Show("PO has been already allocated to GRN.", "Purchase Order", MessageBoxButtons.OK, MessageBoxIcon.Information);
                        return false;
                    }
                }

           }
            return isSuccess;
        }

        public DataTable GetGoodReceiveReturnTaxData(int primaryKey)
        {
            var readMasterData = DatabaseAccess.Instance;
            string sqlQuery = "Select * from fGrrTaxDt where GrnrPk=@referenceNo";
            var paramList = new List<Parameter>
                                 {
                                     new Parameter{ParamName="@referenceNo",ParamValue=primaryKey},
                                 };


            var grnrTaxQuery = new Query { QueryString = sqlQuery, QType = QueryType.SELECTOTHER, ParameterList = paramList.ToArray() };

            var dtGrnrTaxDet = readMasterData.ExcecuteSelect(grnrTaxQuery, true);
            return dtGrnrTaxDet;

        }

        public bool ValidateQuotation(Modes formMode, int primaryKey)
        {
            bool isSuccess = true;
            string keyCode = string.Empty;
            ReadService readService = new ReadService();
            var db = DatabaseAccess.Instance;

            if (formMode == Modes.Cancel || formMode == Modes.Edit)
            {
                string quoSql = "Select QuoPk,BalQty from FQuodet where Balqty <> Qty and QuoPk=@quoPk";

                var paramList = new List<Parameter>()
                {
                    new Parameter{ParamName="@quoPk",ParamValue=primaryKey},
                };

                var quotationQuery = new Query { QueryString=quoSql, QType = QueryType.SELECTOTHER , ParameterList=paramList.ToArray() };

                if (db.ExcecuteSelect(quotationQuery,true).Rows.Count > 0)
                {
                    MessageBox.Show("Quotation has already been allocated to Sales Order.", "Quotation", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return false;
                }

                List<ColumnWithValue> whereConditionQuoHed = new List<ColumnWithValue>();
                whereConditionQuoHed.Add(new ColumnWithValue { ColumnName = "QuoPk", Comparison = QComparison.EQUALS, ColumnValue = primaryKey });
                List<ColumnWithValue> displayFieldsQuoHed = new List<ColumnWithValue>();
                displayFieldsQuoHed.Add(new ColumnWithValue { ColumnName = "QuoPk" });

                var quoQuery = new Query { TableName = "fOrdHed", QType = QueryType.SELECT, ColValues = displayFieldsQuoHed.ToArray(), WhereClause = whereConditionQuoHed.ToArray() };

                if (db.ExcecuteSelect(quoQuery).Rows.Count > 0)
                {
                    MessageBox.Show("Quotation has already been allocated to Sales Order.", "Quotation", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return false;
                }
                
            }
            return isSuccess;
        }

    }
}
